package org.andao.core.support.excel.file;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.andao.core.support.excel.config.ConfigParam;
import org.andao.core.support.excel.config.ExcelConfigPropertyParam;
import org.andao.core.support.excel.config.MergeCellsParam;
import org.andao.core.utils.ExDateUtils;
import org.andao.core.utils.ExStringUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;


/**
 * model转成excel对象. <p>
 * @author Seewo Software - Marco.hu(huzhiguo@cvte.cn)
 *
 */
public class ModelToExcel {
		protected Logger logger = LoggerFactory.getLogger(getClass());	

	    private WritableCellFormat headerFormat;//excel写入列标题格式

	    private WritableCellFormat titleFormat;//excel标题格式

	    private WritableCellFormat normolFormat;//excel数据默认格式

	    private String header;//标题

	    private int intHeight = -1;//初始高度

	    // 默认按配制文件输出，传入的dynamicTitleMap（property,exceltitlename）
	    // 只是按javabean对应的属性改变输出标题
	    // 如果 isDynamicTitle = true ，按传入的dynamicTitleMap 给出的列输出，只是列显示排序按配制文件设置。
	    // isDynamicTitle = true 要验证，dynamicTitleMap size
	    // 大于0,且其中设置的属性在javabean中存在的列必须大于0
	    private boolean isDynamicTitle = false;

	    private Map dynamicTitleMap = new HashMap();//动态列标题参数
	    	  
	    /*
	     * 文件导出时，支持模板导出
	     * isTemplate = true ,按传入的模板文件格式输出，　
	     * templateFile　模板文件，
	     * startRow　开始输出的行数，
	     * paramMap　模板中的参数。map.put("Name","尹景民")如模板中有一个单元格为　　制表人　#Name# ,输出时为：制表人　尹景民
	     */
	    private boolean isTemplate = false;
	    
	    private String templateFile = null;//模板文件名
	    
	    private int startRow = 0;//起始行
	    
	    private Map paramMap = new HashMap();//参数
	    
	    private int startColumn = 0;//起始列
	    
	    private boolean isInsertRow = true;//是否允许插入行
	    	    
	    //支持多Sheet输出
	    private String sheetName="Sheet1";
	    
	    private int sheetNum=0;//输出的sheet数量

	    private ConfigParam param;//配置参数
	    
	   // private boolean isCellAutoHeight = false;//是否需要自动计算单元换行高度
	    
	    public ModelToExcel() {

	    }
	    
	    /**
	     * 构造方法
	     * @param param 配置参数
	     */
	    public ModelToExcel(ConfigParam param){
	    	this.param = param;
	    }
	   	
	    /**
	     * 初始化头部样式
	     *
	     */
	    private void initFormat() {
	        if (this.headerFormat == null) {
	            this.headerFormat = this.getDefaultHeaderFormat();
	        }
	        if (this.titleFormat == null) {
	            this.titleFormat = this.getDefaultTitleFormat();
	        }
	        if (this.normolFormat == null) {
	            this.normolFormat = this.getDefaultNormolFormat();
	        }
	    }
	    
	    /**
	     * 获取模板文件
	     * @return
	     */
	    public File getExcelfile() {
	        initFormat();
	        if (isDynamicTitle) {
	            return getExcelfileByDynamicTitle(); // 动态传入的表头及列
	        } else if(isTemplate){                  
	            return getExcelfileByTemplate();//模板方式
	        }else{
	            return getExcelfileByConfig(); // 配制文件的列，可改变表头
	        }
	    }
	    
	    /**
	     * 按模板文件方式导出Excel文件
	     * @return 替换模板文件后的文件
	     */
	    public File getExcelfileByTemplate(){
	        try {
	        	//取出目标文件
	            if (param.getExcelFile()!=null&&!param.getExcelFile().exists()) {
	            	param.getExcelFile().createNewFile();
	            }
	            //复制模板内容到导出文件中
	            Workbook book;
	            WritableWorkbook wbook;
	            if(param.getExcelFile()!=null){	                
	                book = Workbook.getWorkbook(new File(this.templateFile)); 
	                wbook = Workbook.createWorkbook(param.getExcelFile() , book);                
	            }else{	              
	                book = Workbook.getWorkbook(new File(this.templateFile));                
	                wbook = Workbook.createWorkbook(param.getExcelOutputStream(),book);
	            }  
	            WritableSheet wsheet;
	            if(wbook.getSheets().length>this.sheetNum){
	                wsheet = wbook.getSheet(this.sheetNum);
	            }else{             	
	                logger.error("模板文件中不存在 Sheet(" + this.sheetNum + ")");                
	                return null;
	            }
	                       
	            
	            int rows = wsheet.getRows();
	            int columns = wsheet.getColumns();
	            
	            //替换模板中 # #　之间的内容。
	            for(int i=0;i<rows;i++){
	                for(int j=0;j<columns;j++){                    
	                    WritableCell wc = wsheet.getWritableCell(j, i);
	                    String strCell = wc.getContents().toString();
	                    logger.debug("get strCell :{}" ,strCell );
	                    if(StringUtils.isNotBlank(strCell)&&strCell.indexOf("#")>=0){                        
	                        strCell = this.getParamValue(strCell);
	                        if(wc.getType() == CellType.LABEL){
	                            Label l = (Label)wc;
	                            l.setString(strCell);
	                        }
	                    }
	                }
	            }
	            //行，列重新取值　行取modelList.size()　+ startRow 
	            columns = param.getExcelConfig().getColumnMap().size();            
	            rows = param.getModelList().size() + startRow;
	            
	            String[] propertyName = new String[columns];
	            String[] propertyDataType = new String[columns];
	            String[] codeTableName = new String[columns];
	            for (int j = 0; j < columns; j++) {
	                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
	                // 每列 Model中对应的属性
	                propertyName[j] = propertyBean.getName(); 
	                propertyDataType[j] = propertyBean.getDataType();
	                codeTableName[j] = propertyBean.getCodeTableName();
	            }
	            
	            
	          //处理数据到单元格
	            doFillinDateToCell(wsheet,startRow,rows,columns, propertyName,codeTableName,propertyDataType,true);	
	            
	            book.close();
	            wbook.write();
	            wbook.close();
	        } catch (IOException e) {	          
	            logger.error(e.getMessage(),e.fillInStackTrace());
	        } catch (RowsExceededException e) {	         
	            logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (WriteException e) {	       
	        	logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (BiffException e) {
	        	logger.error(e.getMessage(), e.fillInStackTrace());
	        }
	        return param.getExcelFile();
	    }
	    
	    /**
	     * 动态生成标题方式导出excel文件
	     * @return
	     */
	    public File getExcelfileByDynamicTitle() {
	        try {
	            /*
	             * 在支持多页的过程中，对于每个工作薄不能每次都要从新建立　Workbook.createWorkbook(this.excelFile);
	             * 要从传进入的文件的基础上进行修改
	             * Workbook book = Workbook.getWorkbook(this.excelFile);  
	             * wbook = Workbook.createWorkbook(this.excelFile,book); 
	             * 但是对于刚刚建立的文件，this.excelFile.createNewFile();
	             * 在执行Workbook.getWorkbook(this.excelFile); 时报错。
	             * 所以设立 isNewFileFlag = false;
	             *              
	             */
	            boolean isNewFileFlag = false;
	            if (param.getExcelFile() != null && !param.getExcelFile().exists()) {
	            	param.getExcelFile().createNewFile();
	                isNewFileFlag = true;
	            }
	            if (this.dynamicTitleMap == null) {
	               logger.error("Error:未设置动态列！");
	                return null;
	            }
	            WritableWorkbook wbook;
	            if(param.getExcelFile()!=null){
	                if(isNewFileFlag){
	                    wbook = Workbook.createWorkbook(param.getExcelFile());
	                }else{
	                    Workbook book = Workbook.getWorkbook(param.getExcelFile());
	                    wbook = Workbook.createWorkbook(param.getExcelFile() , book);  
	                }
	            }else{                
	                wbook = Workbook.createWorkbook(param.getExcelOutputStream());
	            }
	            
	            if(wbook.getSheets().length > this.sheetNum){
	                wbook.removeSheet(this.sheetNum);
	            }   
	            
	            WritableSheet wsheet = wbook.createSheet(this.sheetName, this.sheetNum);
	            
	           logger.debug("get sheet name:{},sheet num:{}",this.sheetName , this.sheetNum);
	            
	            int columns = this.dynamicTitleMap.size();
	            // 加上表头2行，标题1行
	            int rows = param.getModelList().size() + 3;
	            logger.debug("get columns : " , columns);
	            
	            // 设置Excel表头
	            wsheet.mergeCells(0, 0, columns - 1, 1);
	            Label cellHeader = new Label(0, 0, this.getHeader(), this.getHeaderFormat());
	            wsheet.addCell(cellHeader);

	            if (intHeight > 0) {
	                wsheet.setRowView(0, intHeight);
	                wsheet.setRowView(1, intHeight);
	            }

	            // String[] propertyName = new String[columns];
	            String[] propertyDataType = new String[columns];
	            String[] codeTableName = new String[columns];
	            for (int j = 0; j < columns; j++) {
	                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
	                // 每列 Model中对应的属性                
	                propertyDataType[j] = propertyBean.getDataType();
	                codeTableName[j] = propertyBean.getCodeTableName();
	            }
	            
	            String[][] title = this.getDynamicTitle();
	            String[] propertyName = title[0];
	            String[] columnWidth = title[1];
	            if (StringUtils.isBlank(propertyName[propertyName.length - 1])) {
	                logger.error("ERROR : 动态表头属性值未在配制文件中配制完全！");
	            }
	            
	            // 设置Excel标题
	            for (int j = 0; j < propertyName.length; j++) {
	                String excelTitleName = (String) this.dynamicTitleMap.get(propertyName[j]);
	                Label cellTitle = new Label(j, 2, excelTitleName, this.getTitleFormat());
	                wsheet.addCell(cellTitle);
	                if (StringUtils.isNotBlank(columnWidth[j])) {
	                    wsheet.setColumnView(j, Integer.parseInt(columnWidth[j]));
	                }
	            }
	            if (intHeight > 0) {
	                wsheet.setRowView(2, intHeight);
	            }
	            
	            //处理数据到单元格
	            doFillinDateToCell(wsheet,3,rows,columns, propertyName,codeTableName,propertyDataType,false);	
	            
	          
	            wbook.write();
	            wbook.close();
	        } catch (IOException e) {
	            logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (RowsExceededException e) {
	        	 logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (WriteException e) {
	        	 logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (BiffException e) {
	        	 logger.error(e.getMessage(), e.fillInStackTrace());
	        }
	        return param.getExcelFile();
	    }

	    /**
	     * 配置文件列方式导出Excel文件，可以动态改变表头
	     * @return
	     */
	    public File getExcelfileByConfig() {	   
	        try {
	            
	            /*
	             * 在支持多页的过程中，对于每个工作薄不能每次都要从新建立　Workbook.createWorkbook(this.excelFile);
	             * 要从传进入的文件的基础上进行修改
	             * Workbook book = Workbook.getWorkbook(this.excelFile);  
	             * wbook = Workbook.createWorkbook(this.excelFile,book); 
	             * 但是对于刚刚建立的文件，this.excelFile.createNewFile();
	             * 在执行Workbook.getWorkbook(this.excelFile); 时报错。
	             * 所以设立 isNewFileFlag = false;
	             *              
	             */
	            
	            boolean isNewFileFlag = false;
	            if (param.getExcelFile() !=null &&  !param.getExcelFile().exists()) {
	            	param.getExcelFile().createNewFile(); 
	                isNewFileFlag = true;
	            }
    
	            WritableWorkbook wbook;
	            if(param.getExcelFile() !=null ){
	                if(isNewFileFlag){
	                    wbook = Workbook.createWorkbook(param.getExcelFile());
	                }else{
	                    Workbook book = Workbook.getWorkbook(param.getExcelFile());                
	                    wbook = Workbook.createWorkbook(param.getExcelFile() , book);  
	                }
	                              
	            }else{                
	                wbook = Workbook.createWorkbook(param.getExcelOutputStream());
	            }
	            
	            if(wbook.getSheets().length > this.sheetNum){
	                wbook.removeSheet(this.sheetNum);
	            }            
	            WritableSheet wsheet = wbook.createSheet(this.sheetName, this.sheetNum);
	            logger.debug("get sheet name:{},sheet num:{}",this.sheetName , this.sheetNum);
	            
	            int columns = param.getExcelConfig().getColumnMap().size();
	            // 加上表头2行，标题1行
	            int rows = param.getModelList().size() + 3;
	            logger.debug("columns :{} " , columns);
	            
	            // 设置Excel表头
	            wsheet.mergeCells(0, 0, columns - 1, 1);
	            Label cellHeader = new Label(0, 0, this.getHeader(), this.getHeaderFormat());
	            wsheet.addCell(cellHeader);

	            if (intHeight > 0) {
	                wsheet.setRowView(0, intHeight);
	                wsheet.setRowView(1, intHeight);
	            }

	            String[] propertyName = new String[columns];
	            String[] propertyDataType = new String[columns];
	            String[] codeTableName = new String[columns];	            
	           
	            // 设置Excel标题
	            for (int j = 0; j < columns; j++) {
	                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
	                String excelTitleName = propertyBean.getExcelTitleName();
	                String name = propertyBean.getName().trim();
	                
	                //每列 Model中对应的属性
	                propertyName[j] = propertyBean.getName();
	                propertyDataType[j] = propertyBean.getDataType();
	                codeTableName[j] = propertyBean.getCodeTableName();
	              	                
	                // 如果动态表头含有记录属性值，取动态传入的表头的值，但是传入的值不能为空，如果为空，取配制文件的值
	                if (this.dynamicTitleMap.containsKey(name)) {
	                    String strTitle = (String) this.dynamicTitleMap.get(name);
	                    if (StringUtils.isNotBlank(strTitle)) {
	                        excelTitleName = strTitle;
	                    }
	                }
	                Label cellTitle = new Label(j, 2, excelTitleName, this.getTitleFormat());
	              	wsheet.addCell(cellTitle);	                
	                
	                // 设置每列的宽度
	                if (StringUtils.isNotBlank(propertyBean.getColumnWidth())) {
	                    wsheet.setColumnView(j, Integer.parseInt(propertyBean.getColumnWidth()));
	                }
	            }

	            if (intHeight > 0) {
	                wsheet.setRowView(2, intHeight);
	            }
	            
	            //填充数据到单元格
	            doFillinDateToCell(wsheet,3,rows,columns, propertyName,codeTableName,propertyDataType,false);						
	            //区分 只有学籍状态统计学籍才使用以下合并单元格的功能
	            if("按学籍状态进行统计——学籍信息统计导出表".equals(this.getHeader())){
		            List<Integer> columnToMerge = new ArrayList<Integer>(0);
		            columnToMerge.add(0);
		            mergeCellByColumnNum(wsheet,3,columnToMerge);
	            }
	            wbook.write();
	            wbook.close();
	    
	        } catch (IOException e) {
	           logger.error(e.getMessage(),e.fillInStackTrace());
	        } catch (RowsExceededException e) {
	        	logger.error(e.getMessage(),e.fillInStackTrace());
	        } catch (WriteException e) {
	        	logger.error(e.getMessage(),e.fillInStackTrace());
	        } catch (BiffException e) {
	        	logger.error(e.getMessage(),e.fillInStackTrace());
	        }
	        return param.getExcelFile();
	    }
	    
	    /*
	     * 填充单元格数据
	     */
	    private void doFillinDateToCell(WritableSheet wsheet,
	    								int startRow,int endRow,
	    								int columns,String[] propertyName,
	    								String[] codeTableName,
	    								String[] propertyDataType,
	    								boolean isTemplate) throws RowsExceededException, WriteException{
	    	boolean isMap = false;
            if (param.getModelList().size()>0) {
                Object tmpObj = param.getModelList().get(0);
                if(tmpObj instanceof Map){
                    isMap = true;
                }
            }
	    	for (int i = startRow; i < endRow; i++) {//循环行 
	    		if(isTemplate){
	    			  //每一次操作，都要插入一行
	                if(this.isInsertRow){
	                    wsheet.insertRow(i);
	                } 
	    		}
                Object modelObj = param.getModelList().get(i - startRow);
                BeanWrapper bw = null;
                if(isMap!=true){   
                     bw= new BeanWrapperImpl(modelObj);
                }
                for (int j = 0; j < columns; j++) {//循环列
                    String strCell = "";
                    Object obj;
                    if(isMap ==true){
                        obj =((Map) modelObj).get(propertyName[j]);
                    }else{
                         obj= bw.getPropertyValue(propertyName[j]);
                    }  
                    if (obj != null) {
                        strCell = obj.toString().trim();//默认转成String
                        //如果为日期类型，则转换对应的pattern格式
                        if(obj instanceof Date && propertyDataType[j].indexOf("Date")>=0){                        	
                        	 String[] strTemp = propertyDataType[j].split("\\ ");
                        	 String pattern = "";
                        	 if(strTemp.length > 1){
                        		 pattern = strTemp[1];
                        	 }      
                        	 //校验
                        	if(ExStringUtils.isNotBlank(pattern)){                    	
    							try {    							
    								strCell = ExDateUtils.formatDateStr((Date)obj, pattern);
    							} catch (ParseException e) {
    								logger.error("转换日期出错:{}",e);
    							}                   		
                        	}
                        }
                    }
                    
                    logger.debug("get strCell:{}",strCell);
                    
                  //转换键值对 
                    if(null != codeTableName[j] && codeTableName[j].length()>1 && null != param.getValueMap()){
                    	strCell = (String)this.param.getValueMap().get(codeTableName[j]+"_"+strCell);	                    	
                    }                                   
                    
                    Label cellNormol = new Label(j, i, strCell, this.getNormolFormat());
                   
                    
                    if(NumberUtils.isNumber(strCell)&&(propertyDataType[j].indexOf("Integer")>=0)){
                        Number ncellNormol;
                        if(strCell.indexOf(".")>=0){                        	
                            ncellNormol = new Number(this.startColumn + j, i, Double.parseDouble(strCell), this.getNormolFormat()); 
                        }else{
                            ncellNormol = new Number(this.startColumn + j, i, Long.parseLong(strCell), this.getNormolFormat());                            
                        }   
                        wsheet.addCell(ncellNormol);
                  }else{
                        wsheet.addCell(cellNormol);
                    }  
                }
                if (intHeight > 0) {
                    wsheet.setRowView(i, intHeight);
                }
            }
	    	//2011-9-27 新增合并单元格
            if(null != param.getMergeCellsParams() && !param.getMergeCellsParams().isEmpty()){
            	for (MergeCellsParam mergeCellsParam : param.getMergeCellsParams()) {
            		
            		wsheet.mergeCells(mergeCellsParam.getStartColumnNum(), mergeCellsParam.getStartRowNum(),
            				mergeCellsParam.getEndColumnNum(), mergeCellsParam.getEndRowNum());
				}
            }
	    }
	    
	    /**
	     * 获取动态标题
	     * @return
	     */
	    private String[][] getDynamicTitle() {
	        int intColumn = param.getExcelConfig().getColumnMap().size();
	        String[][] propertyName = new String[2][this.dynamicTitleMap.size()];

	        int j = -1;
	        for (int i = 0; i < intColumn; i++) {
	            ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(i + 1));
	            String name = propertyBean.getName().trim();
	            String columnWidth = propertyBean.getColumnWidth();
	            if (this.dynamicTitleMap.containsKey(name)) {
	                j++;
	                propertyName[0][j] = name;
	                propertyName[1][j] = columnWidth;

	            }
	        }

	        // 这里可以判断j的值，来判断动态传入的map属性设置是否正确，
	        if (j+1 != this.dynamicTitleMap.size()) {
	           logger.warn("动态表头属性值未在配制文件中配制！");
	        }
	        return propertyName;
	    }

	    public void setHeaderCellFormat(WritableCellFormat headerFormat) {	     
	        this.headerFormat = headerFormat;
	    }

	    public void setNormolCellFormat(WritableCellFormat normolFormat) {	   
	        this.normolFormat = normolFormat;
	    }

	    public void setRowHeight(int intHeight) {	  
	        this.intHeight = intHeight;
	    }

	    public void setTitleCellFormat(WritableCellFormat titleFormat) {	 
	        this.titleFormat = titleFormat;
	    }

	    public void setHeader(String header) {	     
	        this.header = header;
	    }

	    public String getHeader() {
	        return header;
	    }

	    public WritableCellFormat getHeaderFormat() {
	        return headerFormat;
	    }

	    public int getIntHeight() {
	        return intHeight;
	    }

	    public WritableCellFormat getNormolFormat() {
	        return normolFormat;
	    }

	    public WritableCellFormat getTitleFormat() {
	        return titleFormat;
	    }

	    /**
	     * 获取默认头部样式
	     * @return
	     */
	    private WritableCellFormat getDefaultHeaderFormat() {
	        WritableFont font = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD);// 定义字体
	        try {
	            font.setColour(Colour.BLACK);// 蓝色字体
	        } catch (WriteException e1) {	      
	            logger.error(e1.getMessage(),e1.fillInStackTrace());
	        }
	        WritableCellFormat format = new WritableCellFormat(font);
	        try {
	            format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
	            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
	            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
	            // format.setBackground(Colour.YELLOW);// 黄色背景
	            format.setBackground(Colour.GRAY_50);

	        } catch (WriteException e) {
	        	logger.error(e.getMessage(),e.fillInStackTrace());
	        }
	        return format;
	    }

	    /**
	     * 获取默认标题样式
	     * @return
	     */
	    private WritableCellFormat getDefaultTitleFormat() {
	        // 字体：TIMES,大小为12号,粗体
	        WritableFont font = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
	        try {
	            font.setColour(Colour.BLACK);// 蓝色字体
	        } catch (WriteException e1) {
	           logger.error(e1.getMessage(),e1.fillInStackTrace());
	        }
	        WritableCellFormat format = new WritableCellFormat(font);

	        try {
	            format.setAlignment(jxl.format.Alignment.CENTRE);
	            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	            format.setBackground(Colour.YELLOW);
	        } catch (WriteException e) {
	           logger.error(e.getMessage(),e.fillInStackTrace());
	        }
	        return format;
	    }

	    /**
	     * 获取默认内容样式
	     * @return
	     */
	    private WritableCellFormat getDefaultNormolFormat() {
	        WritableFont font = new WritableFont(WritableFont.TIMES, 10);
	        WritableCellFormat format = new WritableCellFormat(font);
	        try {
	            format.setAlignment(jxl.format.Alignment.CENTRE);
	            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
	            format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	        } catch (WriteException e) {
	            logger.error(e.getMessage(),e.fillInStackTrace());
	        }
	        return format;
	    }

	  	
	    /**
	     * 得到参数值
	     * @param strTemp
	     * @return
	     */
	    private String getParamValue(String strTemp){
	        String str = strTemp;
	        Object[] keyarr = this.paramMap.keySet().toArray();
	        for(int i=0;i<keyarr.length;i++){
	            String strkey = "#" + keyarr[i].toString() + "#";
	            str = StringUtils.replace(str, strkey, paramMap.get(keyarr[i]).toString());
	        }
	        return str;
	    }
	    
	    public Map getDynamicTitleMap() {
	        return dynamicTitleMap;
	    }

	    public void setDynamicTitleMap(Map dynamicTitleMap) {
	        this.dynamicTitleMap = dynamicTitleMap;
	    }

	    public boolean isDynamicTitle() {
	        return isDynamicTitle;
	    }

	    public void setDynamicTitle(boolean isDynamicTitle) {
	        this.isDynamicTitle = isDynamicTitle;
	    }

	    public void setTemplateParam(String templateFile, int startRow, Map paramMap){        
	        this.setTemplateParam(true, templateFile, startRow, paramMap);
	    }
	    public void setTemplateParam(boolean isTemplate,String templateFile,int startRow,Map paramMap){
	        this.setTemplateParam(isTemplate, templateFile, startRow, 0, paramMap, true);
	    }
	    
	    //edit YJM　2008-03-29
	    public void setTemplateParam(String templateFile,int startRow,int startColumn,Map paramMap,boolean isInsertRow){
	        this.setTemplateParam(true, templateFile, startRow, startColumn, paramMap, isInsertRow);
	    }
	    
	    /**
	     * 设置模板参数
	     * @param isTemplate
	     * @param templateFile
	     * @param startRow
	     * @param startColumn
	     * @param paramMap
	     * @param isInsertRow
	     */
	    public void setTemplateParam(boolean isTemplate,String templateFile,int startRow,int startColumn,Map paramMap,boolean isInsertRow){
	        this.isTemplate = isTemplate;
	        this.templateFile = templateFile;
	        this.startRow = startRow;
	        this.paramMap = paramMap;
	        this.startColumn = startColumn;
	        this.isInsertRow = isInsertRow;
	    }
	    
	    public Map getParamMap() {
	        return paramMap;
	    }

	    public void setParamMap(Map paramMap) {
	        this.paramMap = paramMap;
	    }

	    public int getStartRow() {
	        return startRow;
	    }

	    public void setStartRow(int startRow) {
	        this.startRow = startRow;
	    }

	    public String getTemplateFile() {
	        return templateFile;
	    }

	    public void setTemplateFile(String templateFile) {
	        this.templateFile = templateFile;
	    }

	    public boolean isTemplate() {
	        return isTemplate;
	    }

	    public void setTemplate(boolean isTemplate) {
	        this.isTemplate = isTemplate;
	    }
	    
	    public void setSheet(int sheetNum, String sheetName) {
	        this.sheetName=sheetName;
	        this.sheetNum=sheetNum;
	    }
	       
	    
	    /**
		 * @return the isCellAutoHeight
		 */
//		public boolean isCellAutoHeight() {
//			return isCellAutoHeight;
//		}
//
//		/**
//		 * @param isCellAutoHeight the isCellAutoHeight to set
//		 */
//		public void setCellAutoHeight(boolean isCellAutoHeight) {
//			this.isCellAutoHeight = isCellAutoHeight;
//		}

		public static void main(String[] args) {
//	        List modelList = new ArrayList();
//	        for (int i = 0; i < 65; i++) {
//	            DeptModel dept = new DeptModel();
//	            dept.setDeptName("总部");
//	            dept.setDeptCode("A10" + i);
//	            dept.setDeptNo(i);
//	            dept.setSendFileName("交南发");
//	            modelList.add(dept);
//	        }
//	        // Colour[] color = Colour.getAllColours();
//	        // for(int i=0;i<color.length;i++){
//	        // System.out.println(color[i].getDescription() + " = " +
//	        // color[i].getDefaultRGB().getRed() + " "
//	        // +color[i].getDefaultRGB().getGreen() +" " +
//	        // color[i].getDefaultRGB().getBlue());
//	        // }
//	        Map map = new HashMap();
//	        map.put("deptName", "部门名称1");
//	        map.put("deptCode", "部门编号1");
//	        map.put("deptNo", "排序1");
//
//	        ExcelConfigManager configManager = ExcelConfigFactory.createExcelConfigManger();
//	        File excelfile = new File("E:\\workspace\\modeltoexcel.xls");
//	        ModelToExcelImpl mte = new ModelToExcelImpl(excelfile, configManager.getModel("deptModel", ""), modelList);
//	        mte.setHeader("部门发文简称（2007）");
//	        mte.setRowHeight(500);
//	        mte.setDynamicTitle(true);
//	        mte.setDynamicTitleMap(map);
//	        mte.getExcelfile();

	    }
		/**
	     * 按模板文件方式导出Excel文件——试室安排总表(合并单元格)
	     * @return 替换模板文件后的文件
	     */
	    public File getExcelfileByTemplate2(){
	    	initFormat();
	    	try {
	        	//取出目标文件
	            if (param.getExcelFile()!=null&&!param.getExcelFile().exists()) {
	            	param.getExcelFile().createNewFile();
	            }
	            //复制模板内容到导出文件中
	            Workbook book;
	            WritableWorkbook wbook;
	            if(param.getExcelFile()!=null){	                
	                book = Workbook.getWorkbook(new File(this.templateFile)); 
	                wbook = Workbook.createWorkbook(param.getExcelFile() , book);                
	            }else{	              
	                book = Workbook.getWorkbook(new File(this.templateFile));                
	                wbook = Workbook.createWorkbook(param.getExcelOutputStream(),book);
	            }  
	            WritableSheet wsheet;
	            if(wbook.getSheets().length>this.sheetNum){
	                wsheet = wbook.getSheet(this.sheetNum);
	            }else{             	
	                logger.error("模板文件中不存在 Sheet(" + this.sheetNum + ")");                
	                return null;
	            }
	            int rows = wsheet.getRows();
	            int columns = wsheet.getColumns();
	            //替换模板中 # #　之间的内容。
	            for(int i=0;i<rows;i++){
	                for(int j=0;j<columns;j++){                    
	                    WritableCell wc = wsheet.getWritableCell(j, i);
	                    String strCell = wc.getContents().toString();
	                    logger.debug("get strCell :{}" ,strCell );
	                    if(StringUtils.isNotBlank(strCell)&&strCell.indexOf("#")>=0){                        
	                        strCell = this.getParamValue(strCell);
	                        if(wc.getType() == CellType.LABEL){
	                            Label l = (Label)wc;
	                            l.setString(strCell);
	                        }
	                    }
	                }
	            }
	            //行，列重新取值　行取modelList.size()　+ startRow 
	            columns = param.getExcelConfig().getColumnMap().size();            
	            rows = param.getModelList().size() + startRow;
	            
	            String[] propertyName = new String[columns];
	            String[] propertyDataType = new String[columns];
	            String[] codeTableName = new String[columns];
	            for (int j = 0; j < columns; j++) {
	                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
	                // 每列 Model中对应的属性
	                propertyName[j] = propertyBean.getName(); 
	                propertyDataType[j] = propertyBean.getDataType();
	                codeTableName[j] = propertyBean.getCodeTableName();
	            }
	            //处理数据到单元格
	            doFillinDateToCell(wsheet,startRow,rows,columns, propertyName,codeTableName,propertyDataType,true);	
	            //合并单元格操作
	            int row = wsheet.getRows();
	            List<Map<Integer,Integer>> list = new ArrayList<Map<Integer,Integer>>(0);
	            for(int j = 0 ; j < 4 ; j++){
	            	Integer start=3  ;
	            	Integer end;
	            	String pre="";
	            	String after="";
	            	for(int i = 3 ; i < row ; i++){
	            		Cell[] cells=wsheet.getRow(i);
	            		if(i==3){
	            			pre = cells[j].getContents();
	            			start = i;
	            			continue;
	            		}else{
	            			after = cells[j].getContents();
	            			if(after.equals(pre)){
	            				if(i==row-1){
	            					wsheet.mergeCells(j, start, j, i);
	            					continue;
	            				}
	            				continue;
	            			}else{
	            				end = i-1;
	            				wsheet.mergeCells(j, start, j, end);
	            				start=i;
	            				pre = cells[j].getContents();
	            			}
	            		}
		            }
	            }
	            book.close();
	            wbook.write();
	            wbook.close();
	        } catch (IOException e) {	          
	            logger.error(e.getMessage(),e.fillInStackTrace());
	        } catch (RowsExceededException e) {	         
	            logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (WriteException e) {	       
	        	logger.error(e.getMessage(), e.fillInStackTrace());
	        } catch (BiffException e) {
	        	logger.error(e.getMessage(), e.fillInStackTrace());
	        }
	        return param.getExcelFile();
	    }
	    /**
	     * 按模板文件方式导出带多个Sheet的Excel文件 (datas与params的key必须保持一至)
	     * @param datas
	     * @param params
	     * @return
	     */
	    public File getExcelfileByTemplateForSheets1(Map<String, List<Object>> datas,Map<String, Object> params){
	    	initFormat(); 
	    	try {
		        	//取出目标文件
		            if (param.getExcelFile()!=null&&!param.getExcelFile().exists()) {
		            	param.getExcelFile().createNewFile();
		            }
		            //复制模板内容到导出文件中
		            Workbook book;
		            WritableWorkbook wbook;
		            if(param.getExcelFile()!=null){	                
		                book = Workbook.getWorkbook(new File(this.templateFile)); 
		                wbook = Workbook.createWorkbook(param.getExcelFile() , book);                
		            }else{	              
		                book = Workbook.getWorkbook(new File(this.templateFile));                
		                wbook = Workbook.createWorkbook(param.getExcelOutputStream(),book);
		            }  
		            
		            int curSheetNum = 0;
		            
		            for(String key: datas.keySet()){
		            	
		            	List newList  = datas.get(key);
		            	this.sheetNum = curSheetNum;
		            	this.paramMap = (Map) params.get(key);
		            	
			            param.setModelList(newList);
			            WritableSheet wsheet;
			            wsheet = wbook.getSheet(this.sheetNum);
			            //当不为最后一页时，就拷贝新页。
			            if(curSheetNum!= (datas.values().size()-1)){
			            	wbook.copySheet(this.sheetNum,String.valueOf(this.sheetNum+1), this.sheetNum+1);
			            }			                       
			            int rows = wsheet.getRows();
			            int columns = wsheet.getColumns();
			            
			            for(int i=0;i<rows;i++){
			                for(int j=0;j<columns;j++){                    
			                    WritableCell wc = wsheet.getWritableCell(j, i);
			                    String strCell = wc.getContents().toString();
			                    logger.debug("get strCell :{}" ,strCell );
			                    if(StringUtils.isNotBlank(strCell)&&strCell.indexOf("#")>=0){                        
			                        strCell = this.getParamValue(strCell);
			                        if(wc.getType() == CellType.LABEL){
			                            Label l = (Label)wc;
			                            l.setString(strCell);
			                        }
			                    }
			                }
			            }
			            //行，列重新取值　行取modelList.size()　+ startRow 
			            columns = param.getExcelConfig().getColumnMap().size();            
			            rows    = param.getModelList().size() + startRow;
			            
			            String[] propertyName     = new String[columns];
			            String[] propertyDataType = new String[columns];
			            String[] codeTableName = new String[columns];
			            for (int j = 0; j < columns; j++) {
			                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
			                // 每列 Model中对应的属性
			                propertyName[j] = propertyBean.getName(); 
			                propertyDataType[j] = propertyBean.getDataType();
			                codeTableName[j] = propertyBean.getCodeTableName();
			            }
			            
			          //处理数据到单元格
			            doFillinDateToCell(wsheet,startRow,rows,columns, propertyName,codeTableName,propertyDataType,true);	
			            curSheetNum ++;
		            }
		            book.close();
		            wbook.write();
		            wbook.close();
		        } catch (IOException e) {	          
		            logger.error(e.getMessage(),e.fillInStackTrace());
		        } catch (RowsExceededException e) {	         
		            logger.error(e.getMessage(), e.fillInStackTrace());
		        } catch (WriteException e) {	       
		        	logger.error(e.getMessage(), e.fillInStackTrace());
		        } catch (BiffException e) {
		        	logger.error(e.getMessage(), e.fillInStackTrace());
		        }
		        return param.getExcelFile();
	    }
	    /**
	     * 按模板文件方式导出带多个Sheet的Excel文件
	     * @return 替换模板文件后的文件
	     */
	    public File getExcelfileByTemplateForSheets(List<String> list,String str){
	    	initFormat(); 
	    	try {
		        	//取出目标文件
		            if (param.getExcelFile()!=null&&!param.getExcelFile().exists()) {
		            	param.getExcelFile().createNewFile();
		            }
		            //复制模板内容到导出文件中
		            Workbook book;
		            WritableWorkbook wbook;
		            if(param.getExcelFile()!=null){	                
		                book = Workbook.getWorkbook(new File(this.templateFile)); 
		                wbook = Workbook.createWorkbook(param.getExcelFile() , book);                
		            }else{	              
		                book = Workbook.getWorkbook(new File(this.templateFile));                
		                wbook = Workbook.createWorkbook(param.getExcelOutputStream(),book);
		            }  
		            List modelList=param.getModelList();
		            
		            for(int num=0 ;num<list.size();num++){
		            	this.sheetNum=num;
		            	List newList = new ArrayList(0);
		            	String range = list.get(num);
		            	Integer begin = Integer.valueOf(range.split("#")[0]);
		            	Integer end   = Integer.valueOf(range.split("#")[1]);
		            	if ("entranceExam".equals(str)) {
		            		//加入层次名和专业名
			            	this.paramMap.put("classicName", range.split("#")[2]);
			            	this.paramMap.put("recruitMajorName", range.split("#")[3]);
						}
		                if ("finalExam".equals(str)){
//原来写成E+课程编码，以此生成的考试编码有误 应为edu_teach_examinfo的examcoursecode		this.paramMap.put("examCode","E"+range.split("#")[2]);//考试批次
		                	this.paramMap.put("examCode",range.split("#")[2]);//考试批次
							this.paramMap.put("courseName",range.split("#")[3]);//课程名
							this.paramMap.put("examType",range.split("#")[4]);//考试类型
							this.paramMap.put("unitcodename", range.split("#")[5]+"——"+range.split("#")[6]);//考场编号——名称
							this.paramMap.put("examRoom", range.split("#")[7]);  //考试室
							this.paramMap.put("examDate",range.split("#")[8]);//考试日期
							this.paramMap.put("examTime",range.split("#")[9]);//考试时间
							this.paramMap.put("examMode", range.split("#")[10]);
							this.paramMap.put("num",(Integer.valueOf(range.split("#")[1])-Integer.valueOf(range.split("#")[0])+1));//人数统计
		                }
		            	for (int j = begin ;j<=end;j++) {
							newList.add(modelList.get(j));
						}
			            param.setModelList(newList);
			            WritableSheet wsheet;
			            wsheet = wbook.getSheet(this.sheetNum);
			            //当不为最后一页时，就拷贝新页。
			            if(num!=list.size()-1){
			            	wbook.copySheet(this.sheetNum,String.valueOf(this.sheetNum+1), this.sheetNum+1);
			            }			                       
			            int rows = wsheet.getRows();
			            int columns = wsheet.getColumns();
			            
			            //替换模板中 # #　之间的内容。
			            for(int i=0;i<rows;i++){
			                for(int j=0;j<columns;j++){                    
			                    WritableCell wc = wsheet.getWritableCell(j, i);
			                    String strCell = wc.getContents().toString();
			                    logger.debug("get strCell :{}" ,strCell );
			                    if(StringUtils.isNotBlank(strCell)&&strCell.indexOf("#")>=0){                        
			                        strCell = this.getParamValue(strCell);
			                        if(wc.getType() == CellType.LABEL){
			                            Label l = (Label)wc;
			                            l.setString(strCell);
			                        }
			                    }
			                }
			            }
			            //行，列重新取值　行取modelList.size()　+ startRow 
			            columns = param.getExcelConfig().getColumnMap().size();            
			            rows = param.getModelList().size() + startRow;
			            
			            String[] propertyName = new String[columns];
			            String[] propertyDataType = new String[columns];
			            String[] codeTableName = new String[columns];
			            for (int j = 0; j < columns; j++) {
			                ExcelConfigPropertyParam propertyBean = (ExcelConfigPropertyParam) param.getExcelConfig().getColumnMap().get(String.valueOf(j + 1));
			                // 每列 Model中对应的属性
			                propertyName[j] = propertyBean.getName(); 
			                propertyDataType[j] = propertyBean.getDataType();
			                codeTableName[j] = propertyBean.getCodeTableName();
			            }
			            
			            
			          //处理数据到单元格
			            doFillinDateToCell(wsheet,startRow,rows,columns, propertyName,codeTableName,propertyDataType,true);	
		            }
		            book.close();
		            wbook.write();
		            wbook.close();
		        } catch (IOException e) {	          
		            logger.error(e.getMessage(),e.fillInStackTrace());
		        } catch (RowsExceededException e) {	         
		            logger.error(e.getMessage(), e.fillInStackTrace());
		        } catch (WriteException e) {	       
		        	logger.error(e.getMessage(), e.fillInStackTrace());
		        } catch (BiffException e) {
		        	logger.error(e.getMessage(), e.fillInStackTrace());
		        }
		        return param.getExcelFile();
	    }
	    /**
	     * 通过列数合并该列内容相同的单元格
	     * @param wsheet
	     * @throws WriteException 
	     * @throws RowsExceededException 
	     */
	    private	void mergeCellByColumnNum(WritableSheet wsheet,Integer start,List<Integer> columnsToMerge ) throws RowsExceededException, WriteException{
	    	int row = wsheet.getRows();
	    	int column = wsheet.getColumns();
            List<Map<Integer,Integer>> list = new ArrayList<Map<Integer,Integer>>(0);
            for(int j = 0 ; j < column ; j++){
            	if(!columnsToMerge.contains(j)) {
            		continue;
            	}
            	start = 3  ;
            	Integer end;
            	String pre="";
            	String after="";
            	for(int i = 3 ; i < row ; i++){
            		Cell[] cells=wsheet.getRow(i);
            		if(i==3){
            			pre = cells[j].getContents();
            			start = i;
            			continue;
            		}else{
            			after = cells[j].getContents();
            			if(after.equals(pre)){
            				if(i==row-1){
            					wsheet.mergeCells(j, start, j, i);
            					continue;
            				}
            				continue;
            			}else{
            				end = i-1;
            				wsheet.mergeCells(j, start, j, end);
            				start=i;
            				pre = cells[j].getContents();
            			}
            		}
	            }
            }
	    }
    
}
